/**
 * Created by black on 2017/4/17.
 */

let sqlite3 = require('sqlite3').verbose();
let Preorder = require('../../model/preorder-model');
let db = new sqlite3.Database('./database/king-land.db');
let _pageSize = 10;

let generateSearchSql = function (filter) {
    let pageNo = filter.pageNo;
    let generatedSql = `
    SELECT * FROM preorder WHERE
      1 = 1
      ${filter && filter.season ? 'AND SEASON = ?' : ''}
      ${filter && filter.type && (filter.type != 'all') ? 'AND TYPE = ?' : ''}
      LIMIT ${(pageNo - 1) * _pageSize}, ${_pageSize}
  `;
    return generatedSql;
};

let generatePageSql = function (filter) {
    let pageNo = filter.pageNo;
    let generatedSql = `
    SELECT count(*) as count FROM preorder WHERE
      1 = 1
      ${filter && filter.season ? 'AND SEASON = ?' : ''}
      ${filter && filter.type && (filter.type != 'all') ? 'AND TYPE = ?' : ''}
  `;
    return generatedSql;
};

/**
 *
 * @param filter
 *
 * @param callback
 */

let getPreordersByFilter = function (filter, callback) {
    let preorders = [];``
    let sql = generateSearchSql(filter);
    let pageSql = generatePageSql(filter);
    let sqlParam = [];
    filter.season && sqlParam.push(filter.season);
    filter.type && (filter.type != 'all') && sqlParam.push(filter.type);
    db.serialize(function () {
        let pagePromise = new Promise((resolve, reject) => {
            db.each(pageSql, sqlParam, function (err, p) {
                resolve(p);
            });
        });
        let dataPromise = new Promise((resolve, reject) => {
            db.each(sql, sqlParam, function (err, p) {
                preorders.push(new Preorder(p.id, p.name, p.phone, p.picking_date, p.tour_number, p.book_catering_status, p.remarks, p.create_time));
            }, function () {
                resolve(preorders);
            });
        });

        Promise.all([pagePromise, dataPromise]).then(values => {
            callback(values[1], values[0]['count']);
        });

    });

    let getPreorderById = function (id, callback) {
        let preorders = {};
        db.serialize(function () {
            db.each(`SELECT * FROM news where id = ?`, [id], function (err, p) {
                preorders = new Preorder(p.id, p.name, p.phone, p.picking_date, p.tour_number, p.book_catering_status, p.remarks, p.create_time);
            }, function () {
                callback(preorders);
            });
        });
    };

    let saveOrUpdate = function (preorderModel, callback) {
        db.serialize(function () {
            var insertSql  = `
      INSERT INTO preorder
        values
        (
          $id,
          $name,
          $phone,
          $pickingDate,
          $tourNumber,
          $bookCateringStatus,
          $remarks,
          $createTime('now', '+8 hour')
         );
    `;
            var updateSql = `
      UPDATE preorder SET
        name = $name,
        phone = $phone,
        picking_date = $pickingDate,
        tour_number = $tourNumber,
        book_catering_status = $bookCateringStatus,
        remarks = $remarks,
        create_time = datetime('now', '+8 hour'),
       where id = $id
    `;
            var prepareStmt = null;
            if(preorderModel.id) {
                prepareStmt = db.prepare(updateSql);
            } else {
                prepareStmt = db.prepare(insertSql);
            }
            prepareStmt.run({
                $id: preorderModel.id || null,
                $name: preorderModel.name,
                $phone: preorderModel.phone,
                $pickingDate: preorderModel.pickingDate,
                $tourNumber: preorderModel.tourNumber,
                $bookCateringStatus: preorderModel.bookCateringStatus,
                $remarks: preorderModel.remarks,
                $createTime: preorderModel.createTime
            });
            prepareStmt.finalize();
        });

    };

    let deleteById = function (id, callback) {
        db.serialize(function () {
            db.run(`delete from preorders where id = ?`, [id], callback);
        });
    };

};

module.exports = {
    getPreordersByFilter: getPreordersByFilter,
    getPreorderById: getPreorderById,
    saveOrUpdate: saveOrUpdate,
    deleteById: deleteById
};